package mwa.cs.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.RouteDetail;
import mwa.cs.util.Converter;
import mwa.cs.util.JDate;

/**
 * A class used for managing route_detail
 */
public class RouteDetailDAO {
	/** a database of this route Detail */
	private Database db;
	Converter voConverter = new Converter();

	/**
	 * Instantiate a new object of this class
	 * 
	 * @param db
	 *            a database
	 */
	public RouteDetailDAO(Database db) {
		this.db = db;
	}

	/**
	 * a method for searching all route Detail in database
	 * 
	 * @return all route Detail
	 */
	public List<Map<String, Object>> findAll() {
		String vaSql = "SELECT * FROM route_detail";
		List<Map<String, Object>> result = db.queryList(vaSql);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	/**
	 * a method for adding a route detail into database
	 * 
	 * @param poRouteDetail
	 *            route_detail to add
	 */
	public void addRouteDetail(RouteDetail poRouteDetail) {
		String vaSql = "INSERT INTO route_detail"
				+ " (site_id, zone_id, route_id, seq, account, "
				+ "meter_read_date, insert_user_id, insert_date, insert_time, "
				+ "update_user_id, update_date, update_time)"
				+ " VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			db.insert(vaSql, poRouteDetail.getVnSiteId(),
					poRouteDetail.getVnZoneId(), poRouteDetail.getVnRouteId(),
					poRouteDetail.getVnSeq(), poRouteDetail.getVaAccount(),
					poRouteDetail.getVaMeterReadDate(),
					poRouteDetail.getVnInsertUserId(),
					poRouteDetail.getVaInsertDate(),
					poRouteDetail.getVaInsertTime(),
					poRouteDetail.getVnUpdateUserId(),
					poRouteDetail.getVaUpdateDate(),
					poRouteDetail.getVaUpdateTime());
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * a method for updating a route detail into database
	 * 
	 * @param pnSiteId
	 *            site that customer live
	 * @param paAccount
	 *            that will update
	 * @param poNewRouteDetail
	 *            that will update
	 */
	public void updateRouteDetail(int pnSiteId, String paAccount,
			RouteDetail poNewRouteDetail) {
		String vaSql = "UPDATE route_detail SET zone_id=?, "
				+ "route_id=?, seq=?, meter_read_date=?, "
				+ "update_user_id=?, update_date=?, update_time=? "
				+ "WHERE site_id=? and account=?";
		try {
			db.update(vaSql, poNewRouteDetail.getVnZoneId(),
					poNewRouteDetail.getVnRouteId(),
					poNewRouteDetail.getVnSeq(),
					poNewRouteDetail.getVaMeterReadDate(),
					poNewRouteDetail.getVnUpdateUserId(),
					poNewRouteDetail.getVaUpdateDate(),
					poNewRouteDetail.getVaUpdateTime(), pnSiteId, paAccount);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * find route_detail by site_id, account
	 * 
	 * @param pnSiteId
	 * @param paAccount
	 * @return route_detail
	 */
	public RouteDetail findRouteDetailByAccount(int pnSiteId, String paAccount) {
		String vaSql = "SELECT * FROM route_detail WHERE site_id = ? and account = ?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount);
		if (result != null) {
			String vaInsertUserId = ((BigDecimal) result.get("INSERT_USER_ID"))
					.toString();
			String vaUpdateUserId = ((BigDecimal) result.get("UPDATE_USER_ID"))
					.toString();
			String vaZoneId = ((BigDecimal) result.get("ZONE_ID")).toString();
			String vaRouteId = ((BigDecimal) result.get("ROUTE_ID")).toString();
			String vaSeq = ((BigDecimal) result.get("SEQ")).toString();
			int vnInsertUserId = 0, vnUpdateUserId = 0, vnZoneId = 0, vnRouteId = 0, vnSeq = 0;
			if (vaInsertUserId != null)
				vnInsertUserId = Integer.parseInt(vaInsertUserId);
			if (vaUpdateUserId != null)
				vnUpdateUserId = Integer.parseInt(vaUpdateUserId);
			if (vaZoneId != null)
				vnZoneId = Integer.parseInt(vaZoneId);
			if (vaRouteId != null)
				vnRouteId = Integer.parseInt(vaRouteId);
			if (vaSeq != null)
				vnSeq = Integer.parseInt(vaSeq);
			RouteDetail voRouteDetail = new RouteDetail(pnSiteId, vnZoneId,
					vnRouteId, vnSeq, paAccount,
					(String) result.get("METER_READ_DATE"), vnInsertUserId,
					(String) result.get("INSERT_DATE"),
					(String) result.get("INSERT_TIME"), vnUpdateUserId,
					(String) result.get("UPDATE_DATE"),
					(String) result.get("UPDATE_TIME"));
			if (!(voRouteDetail.getVaMeterReadDate() == null))
				voRouteDetail.setVaMeterReadDate(JDate.showDate(voRouteDetail
						.getVaMeterReadDate()));
			return voRouteDetail;
		} else {
			return null;
		}
	}

	/**
	 * delete route_detail by site_id, account
	 * 
	 * @param pnSiteId
	 *            is an site_id that want to delete data
	 * @param paAccount
	 *            is an account that want to delete data
	 * @return true if success, false if fail
	 */
	public boolean deleteRouteDetailByAccount(int pnSiteId, String paAccount) {
		String vaSql = "DELETE FROM route_detail WHERE site_id = ? and account = ?";
		try {
			db.update(vaSql, pnSiteId, paAccount);
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public List<Map<String, Object>> findManageSequenceRouteDetail(
			int pnSiteId, int pnZoneId, int pnRouteId, String paSortColumn,
			String paSortDirection) {

		String vaSql = "SELECT ROWNUM as rnum,zone.zone_name,route.route_description,route_detail.account,customer.pname||' '||customer.fname||' '||customer.lname AS name,route_detail.seq "
				+ "FROM route_detail,customer,zone,route WHERE route_detail.account = customer.account AND route_detail.zone_id = zone.zone_id "
				+ "AND route_detail.route_id = route.route_id AND route_detail.zone_id = route.zone_id AND route_detail.site_id = ?"
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? ORDER BY "
				+ paSortColumn + " " + paSortDirection + "";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouteId);
		if (result != null) {
			return result;
		} else {
			return null;
		}

	}

	public List<Map<String, Object>> findAddSequenceRouteDetail(int pnSiteId,
			int pnZoneId, int pnRouteId, String paAccount, String paAccountOld,
			String paHomeNumber, String paName, String paSortColumn,
			String paSortDirection) {

		String vaSql = "SELECT ROWNUM as rnum,account,account_old,name,home_number, address, city_code, district_code, province_code, post_code "
				+ "FROM (SELECT route_detail.account,customer.account_old,customer.pname||' ' ||customer.fname||' ' ||customer.lname AS name,customer.home_number, "
				+ "customer.address, customer.city_code, customer.district_code, customer.province_code, customer.post_code "
				+ "FROM route_detail,customer WHERE route_detail.account = customer.account AND route_detail.site_id =? "
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? AND (COALESCE(customer.account, ' ') LIKE '%"
				+ paAccount
				+ "%') "
				+ "AND (COALESCE(customer.account_old, ' ') LIKE '%"
				+ paAccountOld
				+ "%') AND (COALESCE(customer.home_number, ' ') LIKE '%"
				+ paHomeNumber
				+ "%') "
				+ "AND ((COALESCE(customer.fname, ' ') LIKE '%"
				+ paName
				+ "%') OR (COALESCE(customer.lname, ' ') LIKE '%"
				+ paName
				+ "%'))  ORDER BY "
				+ paSortColumn
				+ " "
				+ paSortDirection
				+ ") ";

		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouteId);
		if (result != null) {
			return result;
		} else {
			return null;
		}

	}

	/**
	 * findRouteDetailByLength
	 * 
	 * @param pnSiteId
	 * @param pnZoneId
	 * @param pnRouteId
	 * @param paSortColumn
	 * @param paSortDirection
	 * @param pnStart
	 * @param pnEnd
	 * @return List<Map<String, Object>> RouteDetail
	 */
	public List<Map<String, Object>> findManageSequenceRouteDetailByLength(
			int pnSiteId, int pnZoneId, int pnRouteId, String paSortColumn,
			String paSortDirection, int pnStart, int pnEnd) {

		String vaSql = "SELECT * FROM (SELECT ROWNUM as rnum,zone_name,route_description,account,name,seq "
				+ "FROM (SELECT zone.zone_name,route.route_description,route_detail.account,customer.pname||' '||customer.fname||' '||customer.lname AS name,route_detail.seq "
				+ "FROM route_detail,customer,zone,route WHERE route_detail.account = customer.account AND route_detail.zone_id = zone.zone_id "
				+ "AND route_detail.route_id = route.route_id AND route_detail.zone_id = route.zone_id AND route_detail.site_id =? "
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? ORDER BY "
				+ paSortColumn
				+ " "
				+ paSortDirection
				+ ") WHERE ROWNUM <=?) WHERE rnum >?";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouteId, pnEnd, pnStart);
		if (result != null) {
			return result;
		} else {
			return null;
		}
	}

	public List<Map<String, Object>> findAddSequenceRouteDetailByLength(
			int pnSiteId, int pnZoneId, int pnRouteId, String paAccount,
			String paAccountOld, String paHomeNumber, String paName,
			String paSortColumn, String paSortDirection, int pnStart, int pnEnd) {
		String vaSql = "SELECT * FROM (SELECT ROWNUM as rnum,account,account_old,name,home_number, address, city_code, district_code, province_code, post_code "
				+ "FROM (SELECT route_detail.account,customer.account_old,customer.pname||' ' ||customer.fname||' ' ||customer.lname AS name,"
				+ "customer.home_number, customer.address, customer.city_code, customer.district_code, customer.province_code, customer.post_code "
				+ "FROM route_detail,customer WHERE route_detail.account = customer.account AND route_detail.site_id =? "
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? AND (COALESCE(customer.account, ' ') LIKE '%"
				+ paAccount
				+ "%') "
				+ "AND (COALESCE(customer.account_old, ' ') LIKE '%"
				+ paAccountOld
				+ "%') AND (COALESCE(customer.home_number, ' ') LIKE '%"
				+ paHomeNumber
				+ "%') "
				+ "AND ((COALESCE(customer.fname, ' ') LIKE '%"
				+ paName
				+ "%') OR (COALESCE(customer.lname, ' ') LIKE '%"
				+ paName
				+ "%')) ORDER BY "
				+ paSortColumn
				+ " "
				+ paSortDirection
				+ ") WHERE ROWNUM <=?) WHERE rnum >?";

		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouteId, pnEnd, pnStart);
		if (result != null) {
			return result;

		} else {
			return null;

		}
	}

	/**
	 * getCountRouteDetail
	 * 
	 * @param pnSiteId
	 * @param pnZoneId
	 * @param pnRouteId
	 * @return RouteDeatail count
	 */
	public int getCountManageSequenceRouteDetail(int pnSiteId, int pnZoneId,
			int pnRouteId) {

		String vaSql = "SELECT count(*) FROM route_detail WHERE site_id=? AND zone_id=? AND route_id=?";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId,
				pnRouteId);
		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}

	}

	public int getCountAddSequenceRouteDetail(int pnSiteId, int pnZoneId,
			int pnRouteId, String paAccount, String paAccountOld,
			String paHomeNumber, String paName, String paSortColumn,
			String paSortDirection) {
		String vaSql = "SELECT COUNT(*) "
				+ "FROM (SELECT route_detail.account,customer.account_old,customer.pname||' ' ||customer.fname||' ' ||customer.lname AS name,customer.home_number, "
				+ "customer.address, customer.city_code, customer.district_code, customer.province_code, customer.post_code "
				+ "FROM route_detail,customer WHERE route_detail.account = customer.account  AND route_detail.site_id =? "
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? AND (COALESCE(customer.account, ' ') LIKE '%"
				+ paAccount + "%') "
				+ "AND (COALESCE(customer.account_old, ' ') LIKE '%"
				+ paAccountOld
				+ "%') AND (COALESCE(customer.home_number, ' ') LIKE '%"
				+ paHomeNumber + "%') "
				+ "AND ((COALESCE(customer.fname, ' ') LIKE '%" + paName
				+ "%') OR (COALESCE(customer.lname, ' ') LIKE '%" + paName
				+ "%')) ORDER BY " + paSortColumn + " " + paSortDirection + ")";

		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId,
				pnRouteId);

		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}

	}

	/**
	 * findRouteDetailByLength
	 * 
	 * @param pnSiteId
	 * @param pnZoneId
	 * @param pnRouteId
	 * @param paSortColumn
	 * @param paSortDirection
	 * @return RouteDeatail count
	 */
	public int getCountManageSequenceRouteDetailSearch(int pnSiteId,
			int pnZoneId, int pnRouteId, String paSortColumn,
			String paSortDirection) {

		String vaSql = "SELECT COUNT(*) FROM (SELECT zone.zone_name,route.route_description,route_detail.account,customer.pname||' '||customer.fname||' '||customer.lname AS name,route_detail.seq "
				+ "FROM route_detail,customer,zone,route WHERE route_detail.account = customer.account AND route_detail.zone_id = zone.zone_id "
				+ "AND route_detail.route_id = route.route_id AND route_detail.zone_id = route.zone_id AND route_detail.site_id =? "
				+ "AND route_detail.zone_id=? AND route_detail.route_id=? ORDER BY "
				+ paSortColumn + " " + paSortDirection + " )";
		Map<String, Object> result = db.querySingle(vaSql, pnSiteId, pnZoneId,
				pnRouteId);
		if (result != null) {
			return Integer.parseInt(result.get("COUNT(*)").toString());
		} else {
			return 0;
		}

	}

	@SuppressWarnings("static-access")
	public void updateSequenceRouteDetail(int pnUserId, int pnSiteId,
			int pnZoneId, int pnRouteId, int pnSequenceNew, int pnSequenceOld,
			String paAccount) {
		db.beginTransaction();
		try {
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			Map<String, Object> map = new HashMap<String, Object>();
			JDate voJDate = new JDate();
//			CustMonthlyDAO voCustMonthlyDAO = new CustMonthlyDAO(db);
//			String paPeriodMonth = voCustMonthlyDAO.findMaxMonth(pnSiteId);
//			String paPeriodYear = voCustMonthlyDAO.findMaxYear(pnSiteId);
			int seq = 0;

			String vaSql = "SELECT site_id, zone_id, route_id, account, seq FROM route_detail WHERE site_id=? AND zone_id=? AND route_id=?";

			List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
					pnZoneId, pnRouteId);
			if (result != null) {
				for (int i = 0; i < result.size(); i++) {
					seq = Integer.parseInt(result.get(i).get("SEQ").toString());
					map = new HashMap<String, Object>();
					map.put("SITE_ID", result.get(i).get("SITE_ID"));
					map.put("ZONE_ID", result.get(i).get("ZONE_ID"));
					map.put("ROUTE_ID", result.get(i).get("ROUTE_ID"));
					map.put("ACCOUNT", result.get(i).get("ACCOUNT"));
					if (pnSequenceNew > pnSequenceOld) {

						if (seq > pnSequenceNew || seq < pnSequenceOld) {
							map.put("SEQ", seq);
						} else if (seq <= pnSequenceNew && seq > pnSequenceOld) {
							seq = seq - 1;
							map.put("SEQ", seq);
						} else {
							map.put("SEQ", pnSequenceNew);
						}

					} else {

						if (seq < pnSequenceNew || seq > pnSequenceOld) {
							map.put("SEQ", seq);
						} else if (seq >= pnSequenceNew && seq < pnSequenceOld) {
							seq = seq + 1;
							map.put("SEQ", seq);
						} else {
							map.put("SEQ", pnSequenceNew);
						}

					}
					list.add(map);
				}

				for (int i = 0; i < list.size(); i++) {
					map = list.get(i);
					int siteId = Integer
							.parseInt(map.get("SITE_ID").toString());
					int zoneId = Integer
							.parseInt(map.get("ZONE_ID").toString());
					int routeId = Integer.parseInt(map.get("ROUTE_ID")
							.toString());
					int sequence = Integer.parseInt(map.get("SEQ").toString());
					String account = map.get("ACCOUNT").toString();
					String updateDate = voJDate.getDate();
					String updateTime = voJDate.getTime();

					vaSql = "UPDATE route_detail SET site_id=?, zone_id=?, route_id=?, seq=?, update_user_id=?, update_date=?, update_time=? WHERE site_id=? AND zone_id=? AND route_id=? AND account=? ";
					db.update(vaSql, siteId, zoneId, routeId, sequence, pnUserId,
							updateDate, updateTime, siteId, zoneId, routeId,
							account);
				}

			}
/*
			seq = 0;
			vaSql = "SELECT site_id, zone_id, route_id, account, seq FROM cust_monthly WHERE site_id=? AND zone_id=? "
					+ "AND route_id=? AND period_month = ? AND period_year = ?";

			result = db.queryList(vaSql, pnSiteId, pnZoneId, pnRouteId,
					paPeriodMonth, paPeriodYear);
			if (result != null) {
				for (int i = 0; i < result.size(); i++) {
					seq = Integer.parseInt(result.get(i).get("SEQ").toString());
					map = new HashMap<String, Object>();
					map.put("SITE_ID", result.get(i).get("SITE_ID"));
					map.put("ZONE_ID", result.get(i).get("ZONE_ID"));
					map.put("ROUTE_ID", result.get(i).get("ROUTE_ID"));
					map.put("ACCOUNT", result.get(i).get("ACCOUNT"));
					if (pnSequenceNew > pnSequenceOld) {

						if (seq > pnSequenceNew || seq < pnSequenceOld) {
							map.put("SEQ", seq);
						} else if (seq <= pnSequenceNew && seq > pnSequenceOld) {
							seq = seq - 1;
							map.put("SEQ", seq);
						} else {
							map.put("SEQ", pnSequenceNew);
						}

					} else {

						if (seq < pnSequenceNew || seq > pnSequenceOld) {
							map.put("SEQ", seq);
						} else if (seq >= pnSequenceNew && seq < pnSequenceOld) {
							seq = seq + 1;
							map.put("SEQ", seq);
						} else {
							map.put("SEQ", pnSequenceNew);
						}

					}
					list.add(map);
				}

				for (int i = 0; i < list.size(); i++) {
					map = list.get(i);
					int siteId = Integer
							.parseInt(map.get("SITE_ID").toString());
					int zoneId = Integer
							.parseInt(map.get("ZONE_ID").toString());
					int routeId = Integer.parseInt(map.get("ROUTE_ID")
							.toString());
					int sequence = Integer.parseInt(map.get("SEQ").toString());
					String account = map.get("ACCOUNT").toString();
					String updateDate = voJDate.getDate();
					String updateTime = voJDate.getTime();

					vaSql = "UPDATE cust_monthly SET site_id=?, zone_id=?, route_id=?, seq=?, update_user_id=?, "
							+ "update_date=?, update_time=? WHERE site_id=? AND zone_id=? AND route_id=? AND account=? "
							+ "AND period_month = ? AND period_year = ?";
					db.update(vaSql, siteId, zoneId, routeId, sequence, pnUserId,
							updateDate, updateTime, siteId, zoneId, routeId,
							account, paPeriodMonth, paPeriodYear);
				}

			}
*/
			db.commit();

		} catch (Exception e) {
			db.rollback();

		}

	}

	@SuppressWarnings("static-access")
	public void deleteSequenceRouteDetail(int pnUserId, int pnSiteId,
			int pnZoneId, int pnRouteId, String paAccount) {
		db.beginTransaction();
		try {
			JDate voJDate = new JDate();
			CustMonthlyDAO voCustMonthlyDAO = new CustMonthlyDAO(db);
			String paPeriodMonth = voCustMonthlyDAO.findMaxMonth(pnSiteId);
			String paPeriodYear = voCustMonthlyDAO.findMaxYear(pnSiteId);
			String vaSql = "UPDATE route_detail SET zone_id=0, "
					+ "route_id=0, update_user_id=?, update_date=?, update_time=?, seq=0 "
					+ "WHERE site_id=? AND zone_id=? AND route_id=? AND account=? ";

			db.update(vaSql, pnUserId, voJDate.getDate(), voJDate.getTime(),
					pnSiteId, pnZoneId, pnRouteId, paAccount);
			vaSql = "UPDATE cust_monthly SET zone_id=0, "
					+ "route_id=0, update_user_id=?, update_date=?, update_time=?, seq=0 "
					+ "WHERE site_id=? AND zone_id=? AND route_id=? AND account=? AND period_month = ? AND period_year = ?";
			db.update(vaSql, pnUserId, voJDate.getDate(), voJDate.getTime(),
					pnSiteId, pnZoneId, pnRouteId, paAccount, paPeriodMonth,
					paPeriodYear);

			vaSql = "SELECT account FROM route_detail WHERE site_id=? AND zone_id=? AND route_id=? ORDER BY seq asc";

			List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
					pnZoneId, pnRouteId);
			if (result != null) {
				for (int i = 0; i < result.size(); i++) {

					vaSql = "UPDATE route_detail SET update_user_id=?, update_date=?, update_time=?, seq=? "
							+ "WHERE site_id=? AND zone_id=? AND route_id=? AND account=?";
					db.update(vaSql, pnUserId, voJDate.getDate(),
							voJDate.getTime(), i + 1, pnSiteId, pnZoneId,
							pnRouteId, result.get(i).get("ACCOUNT").toString());

				}

			}

			vaSql = "SELECT account FROM route_detail WHERE site_id=? AND zone_id=? AND route_id=? ORDER BY seq asc";

			result = db.queryList(vaSql, pnSiteId, pnZoneId, pnRouteId);
			if (result != null) {
				for (int i = 0; i < result.size(); i++) {

					vaSql = "UPDATE cust_monthly SET update_user_id=?, update_date=?, update_time=?, seq=? "
							+ "WHERE site_id=? AND zone_id=? AND route_id=? AND account=? AND period_month = ? AND period_year = ?";
					db.update(vaSql, pnUserId, voJDate.getDate(),
							voJDate.getTime(), i + 1, pnSiteId, pnZoneId,
							pnRouteId, result.get(i).get("ACCOUNT").toString(),
							paPeriodMonth, paPeriodYear);

				}

			}

			db.commit();

		} catch (Exception e) {
			db.rollback();

		}

	}

	public void updateAddSequenceRouteDetail(int pnUserId, int pnSiteId,
			int pnZoneId, int pnRouteId, int pnSequenceNew, int pnSequenceOld,
			String paAccount) {
		db.beginTransaction();
		try {
			CustMonthlyDAO voCustMonthlyDAO = new CustMonthlyDAO(db);
			String paPeriodMonth = voCustMonthlyDAO.findMaxMonth(pnSiteId);
			String paPeriodYear = voCustMonthlyDAO.findMaxYear(pnSiteId);

			String vaSql = "UPDATE route_detail SET zone_id=?, "
					+ "route_id=?, seq=? WHERE site_id=? AND account=? ";
			db.update(vaSql, pnZoneId, pnRouteId, pnSequenceOld, pnSiteId,
					paAccount);

			vaSql = "UPDATE cust_monthly SET zone_id=?, "
					+ "route_id=?, seq=? WHERE site_id=? AND account=? AND period_month = ? AND period_year = ?";
			db.update(vaSql, pnZoneId, pnRouteId, pnSequenceOld, pnSiteId,
					paAccount, paPeriodMonth, paPeriodYear);

			updateSequenceRouteDetail(pnUserId, pnSiteId, pnZoneId, pnRouteId,
					pnSequenceNew, pnSequenceOld, paAccount);

			db.commit();

		} catch (Exception e) {
			db.rollback();

		}
	}

	public int getLastSequenceRouteDetail(int pnSiteId, int pnZoneId,
			int pnRouteId) {
		String vaSql = "SELECT seq FROM route_detail WHERE site_id=? AND zone_id=? AND route_id=? ORDER BY seq asc";
		List<Map<String, Object>> result = db.queryList(vaSql, pnSiteId,
				pnZoneId, pnRouteId);
		if (result != null) {
			int seq = 0;
			for (int i = 0; i < result.size(); i++) {

				seq = Integer.parseInt(result.get(i).get("SEQ").toString());
			}
			return seq;
		} else {
			return 0;
		}

	}
	
	// บันทึกข้อมูล RouteDetail ในกรณีที่บันทึกผลการติดตั้งประปา
	  	public boolean SaveRouteDetailForInstall(RouteDetail poNewRouteDetail, String paRequestNumber) {
	          String vaSql = "INSERT INTO route_detail ( "
	        		  + "site_id, zone_id, route_id, seq, account, meter_read_date, insert_user_id, insert_date, insert_time) "
	        		  + "SELECT ?, ?, ?, ?, account, ?, ?, ?, ? "
            		  + "FROM request WHERE request_number = ?";
	        
	          boolean result = false;
	          try {
	          	db.update(vaSql,
	          			poNewRouteDetail.getVnSiteId(),
	          			poNewRouteDetail.getVnZoneId(),
	          			poNewRouteDetail.getVnRouteId(),
	          			poNewRouteDetail.getVnSeq(),
	          			poNewRouteDetail.getVaMeterReadDate(),
	          			poNewRouteDetail.getVnInsertUserId(),
	          			poNewRouteDetail.getVaInsertDate(),
	          			poNewRouteDetail.getVaInsertTime(),
	          			paRequestNumber);
	          	result = true;
	          } catch (Exception e) {
	          	result = false;
	          }
	          return result;
	  	}

}
